package com.ruoyi.report.forms.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.common.core.text.Convert;
import com.ruoyi.report.forms.domain.ReportFormsTemplate;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.awt.Color;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExportExcel {

    // 要查询的表名
    public String tableName;

    // 要查询的字段
    public List<String> colNameList;

    // 模板配置对象
    public JSONObject jsonObject;

    // 要生成Excel的数据
    public List<Map<String, Object>> tableData;

    // 模板
    public ReportFormsTemplate template;

    // sheet
    public SXSSFSheet sheet;

    // workbook
    public SXSSFWorkbook workbook;

    // 生成中的表格最大宽度
    private int excelCells = -1;

    // 表格的宽度
    private JSONArray cellsWidth;

    // 表格的高度
    private JSONArray cellsHeight;

    // 单元格的单位宽度(mm)
    private static final float cellWidth = 2.052f;

    // 单元格的单位高度(mm)
    private static final float cellHeight = 0.3528f;

    // 用来匹配查找字段
    private Pattern patternField = Pattern.compile("\\$\\{\\w+\\}");

    // 用来匹配查找公式
    private Pattern patternEquation = Pattern.compile("#\\{[\\w()]+\\}");

    public ExportExcel(ReportFormsTemplate reportFormsTemplate) {
        this.tableName = "";
        this.colNameList = new ArrayList<>();
        this.template = reportFormsTemplate;
        this.jsonObject = JSON.parseObject(this.template.getTemplateConfig());
    }

    public void GetDataConditions() {
        this.tableName = this.jsonObject.getJSONObject("source").getString("view");
        JSONArray jsonArray = this.jsonObject.getJSONArray("cells");
        for (int i = 0; i < jsonArray.size(); i++) {
            // 获取最大列宽
            int col = jsonArray.getJSONObject(i).getInteger("col") - 1;
            int colspan = jsonArray.getJSONObject(i).getInteger("colspan") - 1;
            if (this.excelCells < (col + colspan)) {
                this.excelCells = col + colspan;
            }

            // 获取要查询的字段
            String text = jsonArray.getJSONObject(i).getString("text");
            if (text.contains("$")) {
                Matcher matcher = this.patternField.matcher(text);
                while (matcher.find()) {
                    String match = matcher.group();
                    this.colNameList.add(match.replace("${", "").replace("}", ""));
                }
            }
        }
        this.colNameList = new ArrayList<>(new HashSet<>(this.colNameList));
    }

    public void CreateExcel() {
        String sheetName = this.jsonObject.getJSONObject("source").getString("name");
        JSONArray jsonArray = this.jsonObject.getJSONArray("cells");

        this.cellsWidth = this.jsonObject.getJSONArray("cellsWidth");
        this.cellsHeight = this.jsonObject.getJSONArray("cellsHeight");

        this.workbook = new SXSSFWorkbook(500);
        this.sheet = this.workbook.createSheet();
        this.workbook.setSheetName(0, sheetName);

        int offsetByData = 0;
        for (int i = 0; i < jsonArray.size(); i++) {
            int row = jsonArray.getJSONObject(i).getInteger("row") - 1;
            int col = jsonArray.getJSONObject(i).getInteger("col") - 1;
            int rowspan = jsonArray.getJSONObject(i).getInteger("rowspan") - 1;
            int colspan = jsonArray.getJSONObject(i).getInteger("colspan") - 1;
            String text = jsonArray.getJSONObject(i).getString("text");
            this.CheckRow(row + rowspan + offsetByData);

            if (rowspan > 0 || colspan > 0) {
                this.sheet.addMergedRegion(new CellRangeAddress(row + offsetByData, row + rowspan + offsetByData, col, col + colspan));
            }

            if (text.contains("#")) {
                CellStyle style = this.getCellStyle(jsonArray.getJSONObject(i));
                Cell cell = this.sheet.getRow(row + offsetByData).getCell(col);
                cell.setCellStyle(style);
                this.SetEquation(cell, text);
            } else if (text.contains("$")) {
                int size = this.tableData.size();
                offsetByData = (rowspan + 1) * size - 1;
                this.CheckRow(row + ((rowspan + 1) * size));

                CellStyle style = this.getCellStyle(jsonArray.getJSONObject(i));

                Matcher matcher = this.patternField.matcher(text);
                while (matcher.find()) {
                    String match = matcher.group();
                    for (int m = 0; m < size; m++) {
                        String value = Convert.toStr(this.tableData.get(m).get(match.replace("${", "").replace("}", "")));
                        Cell cell = this.sheet.getRow(row + m).getCell(col);
                        if (cell.getStringCellValue().equals("")) {
                            cell.setCellValue(text);
                        }

                        cell.setCellStyle(style);
                        String tmp = cell.getStringCellValue();
                        cell.setCellValue(tmp.replace(match, value));
                    }
                }
            } else {
                CellStyle style = this.getCellStyle(jsonArray.getJSONObject(i));
                Cell cell = this.sheet.getRow(row + offsetByData).getCell(col);
                cell.setCellStyle(style);
                cell.setCellValue(text);
            }
        }
    }

    public void OutputExcel(HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            this.workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 进行公式的计算
    private void SetEquation(Cell cell, String text) {
        Matcher fieldMatcher = this.patternField.matcher(text);
        if (!fieldMatcher.find()) {
            return;
        }
        String field = fieldMatcher.group();
        text = text.replace(field, "");

        Matcher equationMatcher = this.patternEquation.matcher(text);
        if (!equationMatcher.find()) {
            return;
        }
        String equation = equationMatcher.group().replace("#{", "").replace("}", "").replace("()", "");
        field = field.replace("${", "").replace("}", "");
        switch (equation) {
            case "SUM":
                this.GetEquationSUM(cell, field);
                break;
            case "COUNT":
                this.GetEquationCOUNT(cell);
                break;
            case "AVG":
                this.GetEquationAVG(cell, field);
                break;
        }
    }

    // 求和
    private void GetEquationSUM(Cell cell, String field) {
        double sum = 0;
        for (int m = 0; m < this.tableData.size(); m++) {
            String str = Convert.toStr(this.tableData.get(m).get(field));
            if (NumberUtils.isCreatable(str)) {
                sum += Convert.toDouble(str);
            }
        }
        cell.setCellValue(sum);
    }

    // 总数
    private void GetEquationCOUNT(Cell cell) {
        cell.setCellValue(this.tableData.size());
    }

    // 平均数
    private void GetEquationAVG(Cell cell, String field) {
        double sum = 0;
        for (int m = 0; m < this.tableData.size(); m++) {
            String str = Convert.toStr(this.tableData.get(m).get(field));
            if (NumberUtils.isCreatable(str)) {
                sum += Convert.toDouble(str);
            }
        }
        double avg = sum / this.tableData.size();
        cell.setCellValue(String.format("%.2f", avg));
    }

    private void CheckRow(int nRow) {
        if (this.sheet.getLastRowNum() < nRow) {
            Row row = this.sheet.createRow(this.sheet.getLastRowNum() + 1);

            for (int i = 0; i <= this.excelCells; i++) {
                Cell cell = row.createCell(i);
            }

            this.CheckRow(nRow);
        }
    }

    private CellStyle getCellStyle(JSONObject object) {
        XSSFFont font = (XSSFFont) this.workbook.createFont();
        XSSFCellStyle style = (XSSFCellStyle) this.workbook.createCellStyle();

        // 黑体
        String bold = object.getString("bold");
        if (bold.equals("600")) {
            font.setBold(true);
        }

        // 倾斜
        String italic = object.getString("italic");
        if (italic.equals("italic")) {
            font.setItalic(true);
        }

        // 下划线
        String underLine = object.getString("underline");
        if (underLine.equals("underline")) {
            font.setUnderline((byte) 1);
        }

        // 字号
        short size = object.getShort("size");
        font.setFontHeightInPoints(size);

        // 字体颜色
        String color = object.getString("color");
        font.setColor(new XSSFColor(new Color(Integer.decode("0x" + color.replace("#", ""))), new DefaultIndexedColorMap()));

        // 文本位置
        String align = object.getString("align");
        switch (align) {
            case "left":
                style.setAlignment(HorizontalAlignment.LEFT);
                break;
            case "center":
                style.setAlignment(HorizontalAlignment.CENTER);
                break;
            case "right":
                style.setAlignment(HorizontalAlignment.RIGHT);
                break;
        }
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        // 填充颜色
        String background = object.getString("background");
        style.setFillForegroundColor(new XSSFColor(new Color(Integer.decode("0x" + background.replace("#", ""))), new DefaultIndexedColorMap()));
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 边框颜色
        String bColor = object.getString("bColor");
        if (!bColor.equals("#ccc")) {
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            XSSFColor borderColor = new XSSFColor(new Color(Integer.decode("0x" + bColor.replace("#", ""))), new DefaultIndexedColorMap());
            style.setTopBorderColor(borderColor);
            style.setLeftBorderColor(borderColor);
            style.setRightBorderColor(borderColor);
            style.setBottomBorderColor(borderColor);
        }

        style.setFont(font);

        return style;
    }
}
